﻿
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient


Public Class Interview_Session
    Inherits System.Web.UI.Page
    Dim gTable As String = "StudentSession"
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim sFunction As String = ""

        If Not IsNothing(Request.Form("action")) Then
            sFunction = Request.Form("action")
        End If

        If sFunction = "saveSession" Then
            saveSession()
        End If
    End Sub

    Private Sub saveSession()
        Dim sName As String = ""
        Dim sValue As String = ""
        Dim sSql As String = "UPDATE STUDENT set std_interviewsession = @session where std_number = @studentid"
        Dim strConn As String = ConfigurationManager.ConnectionStrings("Conn").ToString
        Dim conn As New SqlConnection(strConn)
        Dim CMD As New SqlCommand(sSql, conn)
        CMD.Parameters.Add(New SqlParameter("@session", SqlDbType.NVarChar, 255))
        CMD.Parameters.Add(New SqlParameter("@studentid", SqlDbType.NVarChar, 255))

        conn.Open()
        Dim objNameVC As NameValueCollection = Request.Form()
        For i As Integer = 0 To objNameVC.Count - 1
            sName = objNameVC.GetKey(i)
            If sName.IndexOf("Session_") = 0 Then
                sValue = objNameVC.Get(i)
                CMD.Parameters("@session").Value = sValue
                CMD.Parameters("@session").Value = sName.Substring(sName.IndexOf("_", 8) + 1)

                'CMD.ExecuteNonQuery()

            End If
        Next
        conn.Close()
    End Sub



    Private Function postError(ByVal bError As Boolean) As Boolean
        Return bError
    End Function
    Public Function ds_StudentSession(ByVal sTable As String) As DataSet
        Dim ds As DataSet
        Dim sSQL As String
        sSQL = "SELECT * FROM STUDENT LEFT OUTER JOIN WPPGROUP ON STUDENT.Std_WPPGrp = WPPGROUP.WPP_Number ORDER BY WPPGROUP.WPP_Number, STUDENT.Std_Expertise"
        ds = getData(sSQL, sTable)
        Return ds
    End Function


    Public Function getData(ByVal sSql As String, ByVal sTable As String) As DataSet
        Dim strConn As String = ConfigurationManager.ConnectionStrings("Conn").ToString
        Dim conn As New SqlConnection(strConn)
        Dim da As New SqlDataAdapter(sSql, conn)
        conn.Open()
        Dim ds As New DataSet(sTable)
        da.Fill(ds)
        conn.Close()
        Return ds
    End Function

    Private Sub exportToExcel()
        Dim sSqlLecturer As String = "select * from wppgroup w join schedule s on w.wpp_number = s.schd_wppgrp" & _
            "left join lecturer l on schd_ltr_number = l.ltr_number order by w.wpp_number, schd_interviewsession"
        Dim ds As DataSet
        ds = getData(sSqlLecturer, "lecturer")

        Dim sSqlStudent As String = "select * from schedule sc left join student s on s.std_wppgrp = sc.schd_wppgrp"
        ds = getData(sSqlStudent, "student")

        generateSheet(ds.Tables("lecturer"), ds.Tables("student"))
    End Sub
    Private Sub generateSheet(ByVal dtLecurer As DataTable, ByVal dtStudent As DataTable)
        Dim sLocation = Server.MapPath("\ExportTemplate")
        Dim objSumStreamReader As StreamReader
        Dim objSessionStreamReader As StreamReader
        objSumStreamReader = File.OpenText(sLocation + "\sumsheet.xml")
        objSessionStreamReader = File.OpenText(sLocation + "\sessionsheet.xml")

        Dim sSumContent As String = objSumStreamReader.ReadToEnd()
        Dim sSessionContent As String = objSessionStreamReader.ReadToEnd()

        objSessionStreamReader.Close()
        objSumStreamReader.Close()

        Dim iColor As Integer = 21080908
        Dim sGroup As String = ""
        Dim sSession As String = ""
        Dim iCountGroup As Integer = 0
        Dim iCountSession As Integer = 0

        If dtLecurer.Rows.Count() > 0 Then
            Dim dr As DataRow
            For Each dr In dtLecurer.Rows
                If sGroup <> dr("WPP_Number") Or sSession <> dr("schd_interviewsession") Then
                    sGroup = dr("WPP_Number")
                    sSession = dr("schd_interviewsession")
                    iCountGroup = iCountGroup + 1
                    iColor = iColor + 20

                    Dim drTemp() As DataRow = dtLecurer.Select("WPP_number = " + sGroup)
                    iCountSession = drTemp.Count()
                    If iCountSession < 5 Then iCountSession = 5
                    sSumContent += "<Row ss:Index=""3"" ss:AutoFitHeight=""0"" ss:Height=""20.0625""><Cell ss:MergeAcross=""" + iCountSession _
                        + """ ss:StyleID=""m" + iColor + """><Data ss:Type=""String"">" + dr("wpp_startdatetime") + "-" + dr("wpp_enddatetime") + "</Data></Cell></Row>"
                End If
            Next
        End If
    End Sub

End Class